package uit.edu.vn;
import java.io.File;
import java.net.InetAddress;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//import java.sql.Date;
//import java.text.DateFormat;
//import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.servlet.*;
import javax.servlet.http.HttpServlet;

import org.apache.xmlbeans.impl.xb.xsdschema.WhiteSpaceDocument.WhiteSpace.Value;

import antlr.collections.List;

import uit.edu.vn.FileObject;

public class Service extends HttpServlet{
		
		private static int actionBackup = 1;
		private static int actionDelete = 0;
		private static int actionRestore = 2;
		
		private static int fileStatusStored = 1;
		private static int fileStatusDeleted = 0;
		
		private static int userStatusActive = 1;
		private static int userStatusSuspend = 0;
		private static int userStatusDeleted = -1;
		
		private static int contactFile = 1;
		private static int smsFile = 2;
		private static int otherFile = 3;
		
		private static int SumCapacity = 52428800;//~50MB tong dung luong nguoi dung co the luu tru
		
//		private static String server = "http://backupmanager.jelastic.servint.net/ServerWeb/USERS/";
//		private static String server = "http://10.0.2.2:8080/ServerWeb/USERS/";
		private static String server = "";
		
		private static Connection con = null;
		private static String user = "root";
		private static String pass = "root";
//		private static String pass = "1mQ3tQpDJ8";
		private static String database = "BackupNRestore";
		private static String driverName = "com.mysql.jdbc.Driver";
//		private static String serverName = "mariadb-backupmanager.jelastic.servint.net:3306/";
		private static String serverName = "127.0.0.1:3306/";
		
		
		/*ket noi csdl mysql bang jdbc*/
		public static ResultSet executeQuery(String sqlCom) throws ClassNotFoundException, SQLException{
			
			Class.forName(driverName);
			con = DriverManager.getConnection("jdbc:mysql://"+serverName+database,user,pass);			
			Statement stm = con.createStatement();		
			ResultSet rs = stm.executeQuery(sqlCom);//thi hanh lenh select va tra ve ket qua
			return rs;
		}
		public static int executeUpdate(String sql)throws ClassNotFoundException, SQLException{
			int result = 0 ;
			Class.forName(driverName);
			con = DriverManager.getConnection("jdbc:mysql://"+serverName+database,user,pass);
			Statement stm = con.createStatement();	
			result  = stm.executeUpdate(sql);
			con.close();
			return result;//neu thanh cong tra ve so thu tu hang thuc thi
											//neu that bai tra ve 0
		}
		
		
		public static UserObject GetInfor(int userid)throws Exception{
			UserObject userObject = new UserObject();
			ResultSet rs = null;
			String selectDayRes = "select * from UserInformation where userid =  '"+userid+"'";
			String selectCount1 = "select count(*) from FileStore where userid = '"+userid+"' and StatusFile = '"+fileStatusStored+"' and FileType = '"+contactFile+"'";
			String selectCount2 = "select count(*) from FileStore where userid = '"+userid+"' and StatusFile = '"+fileStatusStored+"' and FileType = '"+smsFile+"'";
			String selectCount3 = "select count(*) from FileStore where userid = '"+userid+"' and StatusFile = '"+fileStatusStored+"' and FileType = '"+otherFile+"'";
			rs = executeQuery(selectDayRes);
			if(rs.next()){
				userObject.setDayRegistry(rs.getString("dayRegistry"));
			}
			
			rs = executeQuery(selectCount1);
			if(rs.next()){
				userObject.setNumContact(String.valueOf(rs.getInt(1)));
			}
			
			rs = executeQuery(selectCount2);
			if(rs.next()){
				userObject.setNumSMS(String.valueOf(rs.getInt(1)));
			}
			
			rs = executeQuery(selectCount3);
			if(rs.next()){
				userObject.setNumOther(String.valueOf(rs.getInt(1)));
			}
			//lay tong dung luong cac tap tin da tai len may chu, va dung luong con lai
			//dung luong toi da la 50MB = 52428800Bytes
			int cap = getCapacity(userid);
			//tinh dung luong da luu tru tap tin cua nguoi dung
			userObject.setSumCapacity(String.valueOf(cap));
			//tinh dung luong luu tru con lai
			userObject.setRemainCapacity(String.valueOf(SumCapacity - cap));
			
			return userObject;
		}
		
		public static String ChangePassword(int userid, String newPass) throws ClassNotFoundException, SQLException{
			
			String changePass = "UPDATE UserInformation SET PASSWORD = '"+ newPass +"' where USERID ='"+ userid+"'";			
			String result = "0";
			result = String.valueOf(executeUpdate(changePass));
			return result;
		}
				
		//cap nhat cac thong tin ma nguoi dung chinh sua
		@WebMethod
		public String updateInformation(String userID,String realName, String email1, String email2, String phoneNumber) throws ClassNotFoundException, SQLException{
			String sqlUpdate = "UPDATE USERINFORMATION SET realName = '"+ realName +
					"',email1 = '"+email1+"',email2 ='"+email2+
					"',phoneNumber ='"+Integer.parseInt(phoneNumber)+"'  WHERE userID = '"+userID+"'";
			//1 thanh cong
			//0 that bai
			return (executeUpdate(sqlUpdate) > 0) ? "1" : "0";			
		}
		//
		@WebMethod
		public String SuspendUser(String username,String reason) throws ClassNotFoundException, SQLException{
			String today = getDateTime();
			
			String sqlUpdate = "UPDATE USERINFORMATION SET StatusUser = '"+ userStatusSuspend +
					"',reasonSuspendUser = '"+reason+"',daySuspendUser ='"+today+
					"'  WHERE userName = '"+username+"'";
			//1 thanh cong
			//0 that bai
			return (executeUpdate(sqlUpdate) > 0) ? "1" : "0";
		}		
		
		//
		@WebMethod
		public String deleteUser(String username,String reason) throws ClassNotFoundException, SQLException{
			String today = getDateTime();
			String sqlUpdate = "UPDATE USERINFORMATION SET StatusUser = '"+ userStatusDeleted +
					"',reasonDeletedUser = '"+reason+"',dayDeleteUser ='"+today+
					"' WHERE userName = '"+username+"'";
			//1 thanh cong
			//0 that bai
			return (executeUpdate(sqlUpdate) > 0) ? "1" : "0";
		}
		
		public String deleteFileById(int fileId, String comment){
			String date = getDateTime();
			String sqlUpdate = "UPDATE FileStore SET StatusFile = '"+ fileStatusDeleted +
					"',DayDeleteFile = '"+date+"' WHERE FileID = '"+fileId+"'";						
			try{
				if( (executeUpdate(sqlUpdate)) > 0){
					return (writeUserLog(fileId, actionDelete, comment));
				}
			}catch(Exception ex){
				
			}			
			return "0";
		}		
		
		
		public ArrayList<FileObject> getListFile(int userID, int type) throws SQLException, ClassNotFoundException{
			String sqlSelect = "select * from FileStore where fileType = '"+type+"' AND userID = '"+ userID +"' and StatusFile = '"+fileStatusStored+"'";
			String sql = "select * from USERINFORMATION where USERID = '"+ userID +"'";
			server = "http://10.0.2.2:8080/ServerWeb/USERS/";//dung cho chay tren localhost
//			server = "http://"+getAddress()+"/ServerWeb/USERS/";//dung cho chay qua mang noi bo		
			ResultSet rsuser = executeQuery(sql);
			String username = "";
			if(rsuser.next()){
				username = rsuser.getString("USERNAME").trim();
			}
			ResultSet rs = executeQuery(sqlSelect);
			ArrayList<FileObject> list = new ArrayList<FileObject>();
			try{
				while(rs.next()){		
					int fileID = rs.getInt("FILEID");				
					String fileName = rs.getString("FILENAME").trim();
					int fileType = rs.getInt("FILETYPE");
					int fileSize = rs.getInt("FILESIZE");				
					String pathFile = (server + username+"/" + fileName).trim();
					int status = rs.getInt("STATUSFILE");
					String daybk = rs.getString("DAYBACKUPFILE").trim();
					String note = rs.getString("NOTE").trim();
					if(note.equals(""));	
					note = "ko co ghi chu";
					FileObject f = new FileObject(fileID, userID, fileName,fileType, fileSize, pathFile, status, daybk, note);					
					list.add(f);						
				}
				return list;
			}catch(Exception ex){
				System.out.println("loi khi lay danh ba tap tin da sao luu: "+ex.toString());
			}
			return null;
		}
		
		
		//kiem tra thong tin dang nhap cua nguoi dung
		@WebMethod
		public String CheckLogin(String username, String password) throws SQLException, ClassNotFoundException, Exception
		{
		    String sqlSelect = "select * from USERINFORMATION WHERE USERNAME = '" + username + 
		    					"' and PASSWORD = '" + password + "' and StatusUser = '" +userStatusActive+"'";
			ResultSet rs = executeQuery(sqlSelect);	    			
		    return rs.next()? (rs.getString("USERID").toString().trim()) : "-1";
		}
		
		//danh sach toan bo tai khoan dang hoat dong cua nguoi dung
		@WebMethod
		public String GetUserList() throws ClassNotFoundException, SQLException{
			String list = "";
			int i=0;
			String sqlSelect = "select userName from USERINFORMATION where StatusUser = '"+userStatusActive+"'";
			ResultSet rs = executeQuery(sqlSelect);
			while(rs.next()){
				list += "@"+rs.getString("USERNAME");
			}
			return list;
		}
		
////		ket noi database ms sql
//		public static ResultSet executeQuery(String sqlCom) throws ClassNotFoundException, SQLException{
//			
//			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//			Connection c = DriverManager.getConnection("jdbc:odbc:BackupNRestore","leethanh","");			
//			Statement stm = c.createStatement();		
//			ResultSet rs = stm.executeQuery(sqlCom);//thi hanh lenh select va tra ve ket qua
//			return rs;
//		}
//		public static int executeUpdate(String sql)throws ClassNotFoundException, SQLException{
//			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//			Connection c = DriverManager.getConnection("jdbc:odbc:BackupNRestore","leethanh","");
//			Statement stm = c.createStatement();			
//			return stm.executeUpdate(sql);//neu thanh cong tra ve so thu tu hang thuc thi
//											//neu that bai tra ve 0
//		}
		
		
		

		//kiem tra va tao tai khoan moi cho nguoi dung
		@WebMethod
		public String CreateAccount(String username,String password, String email, String note) throws ClassNotFoundException, SQLException{
			String date = getDateTime();
			String sqlEmail = "select * from USERINFORMATION where email1 = '"+email+"' OR email2 = '"+email+"'";
			String sqlUser = "select * from USERINFORMATION where USERNAME = '"+ username +"'";
			String sqlInsert ="insert into USERINFORMATION(USERNAME,PASSWORD,DAYREGISTRY,EMAIL1,STATUSUSER,NOTE) values" +
					"('"+username+"','"+password+"','"+date+"','"+email+"','"+userStatusActive+"','"+note+"')";									
			if(executeQuery(sqlUser).next()){
				return "0";//tai khoan da ton tai
			}else if(executeQuery(sqlEmail).next()){
				return "-2";// email nay da ton tai
			}else if(executeUpdate(sqlInsert) > 0){			
						return "1";//dang ki tai khoan thanh cong
					}
			return "-1";//dang ky tai khoan that bai
		}
		
		// ghi vao bang UserLog hanh dong cua nguoi dung: sao luu, phuc hoi hoac xoa tap tin
		@WebMethod
		public static String writeUserLog(int fileID, int action, String note) throws ClassNotFoundException, SQLException{
			String date = getDateTime();
			String sqlInsert ="insert into USERLOG ( fileID, action, dayAction, note) values" +
					"('"+fileID+"','"+action+"','"+date+"','"+note+"')";
			return (executeUpdate(sqlInsert)) > 0 ? "1" : "0";			
		}
		
		// ghi vao csdl bang FileStore hanh dong cua nguoi dung khi sao luu tap tin moi, hoac xoa tap tin da sao luu
		@WebMethod
		public static String writeFile(int userid, String fileName,int fileType, int fileSize,String note)throws ClassNotFoundException, SQLException{
			String date = getDateTime();
			String sqlInsert = "insert into FILESTORE (USERID,FILENAME,FILETYPE,FILESIZE,STATUSFILE,DAYBACKUPFILE,NOTE) values"
					+"('"+ userid +"','"+ fileName +"','"+fileType+"','"+fileSize+"','"+fileStatusStored+"','"+date+"','"+note+"')";
			String sqlSelect = "select * from FILESTORE where FILENAME = '"+fileName+"' and USERID = '"+userid+"'";
			if(executeUpdate(sqlInsert) > 0){
				ResultSet rs = executeQuery(sqlSelect);
				if(rs.next()){
					int fileId = rs.getInt("FILEID");
					writeUserLog(fileId, actionBackup, note);
					return "1";
				}		
			}
			return "0";
		}
		
		//lay ten dang nhap(username) cua nguoi dung thong qua userid
		public static String getUsernameById(int userid){
			try{
				String sqlSelect = "select USERNAME from USERINFORMATION where USERID = '"+userid+"'";
				ResultSet rs = executeQuery(sqlSelect);
				if(rs.next())
					return rs.getString("USERNAME");				
			}catch(Exception ex){
				System.out.println("getUsernameById error: "+ex.toString());
				return null;
			}
			return null;

		}
		
		//lay dung luong luu tru con lai cua nguoi dung
		public static int getCapacity(int userid)throws Exception{
			int result = 0;
			String sum = "select * from FileStore where userid = '"+userid+"' and StatusFile ='"+fileStatusStored+"'";
			ResultSet rs = executeQuery(sum);
			while(rs.next()){
				int t = Integer.parseInt(rs.getString("FileSize"));
				result += t;
			}
			return result;
		}
		
//		//lay ngay hien tai cua he thong		
		public static String getDateTime(){
			String DATE_FORMAT_NOW = "dd-MM-yyyy HH-mm-ss";
		    Calendar cal = Calendar.getInstance();
		    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
		    String datetime = sdf.format(cal.getTime());
			return datetime;
		}
		
		public static String getAddress(){
			String add = "";
		    try {
		    	InetAddress thisIp =InetAddress.getLocalHost();
		    	add = thisIp.getHostAddress();		    			    	
		    }catch(Exception e) {
		    	e.printStackTrace();
		    }
		    return add;
		}

}
